package at.spengergasse.dao.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import at.spengergasse.dao.RechnungDao;
import at.spengergasse.dao.exception.DAOException;
import at.spengergasse.dao.exception.NotFoundException;
import at.spengergasse.dao.util.JDBCHelper;

import at.spengergasse.model.Rechnung;

public class RechnungDaoDerbyImpl extends AbstractDAODerbyImpl implements RechnungDao{
	  
	private static final String SQL_INSERT =
	      "INSERT INTO rechnung(r_mod_ts, r_datum, r_kundenid) " +
	      "VALUES (?, ?, ?)";

	  private static final String SQL_UPDATE =
	      "UPDATE rechnung " +
	      "SET r_mod_ts = ?, " +
	      "    r_datum = ? " +
	      "WHERE r_id = ? " +
	      "  AND r_mod_ts = ? ";

	  private static final String SQL_DELETE =
	      "DELETE FROM rechnung " +
	      "WHERE r_id = ? " +
	      "  AND r_mod_ts = ? ";

	  private static final String SQL_SELECT_BY_ID =
	      "SELECT r_id, r_mod_ts, r_datum, r_kundenid " +
	      "FROM rechnung " +
	      "WHERE r_id = ?";

	  private static final String SQL_SELECT_ALL =
	      "SELECT r_id, r_datum, r_kundenid, r_mod_ts " +
	      "FROM rechnung";
	  
	  private static final String SQL_SELECT_BY_DATE =
		  "SELECT r_id, r_kundenid " +
		  "FROM rechnung " +
		  "WHERE r_datum = ?";
	  
	  private static final String SQL_SELECT_BY_KUNDENID =
		  "SELECT r_id, r_datum, r_kundenid, r_mod_ts " +
		  "FROM rechnung " +
		  "WHERE r_kundenid = ?";
	  
	  private PreparedStatement insertStmt = null;
	  private PreparedStatement updateStmt = null;
	  private PreparedStatement deleteStmt = null;
	  private PreparedStatement selectByIdStmt = null;
	  private PreparedStatement selectAllStmt = null;
	  private PreparedStatement selectByDateStmt = null;
	  private PreparedStatement selectByKundenidStmt = null;
	  
	  
	  public int save(Connection connection, Rechnung rechnung) {
		    validateConnection(connection);
		    validateRechnung(rechnung);
		    return (rechnung.isNew()) ? insert(connection, rechnung) : update(connection, rechnung);
		  }

	  private int insert(Connection connection, Rechnung rechnung) {
		  validateConnection(connection);
		  validateRechnung(rechnung);

		  int recordsAffected = 0;
		  Date now = new Date();
		  ResultSet generatedKeys = null;

		  try {
			  if (insertStmt == null) {
				  insertStmt = connection.prepareStatement(SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
		      }

		      insertStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
		      insertStmt.setDate(2, JDBCHelper.toSqlDate(rechnung.getDate()));
		      insertStmt.setLong(3, rechnung.getKundenID());

		      recordsAffected = insertStmt.executeUpdate();
		      generatedKeys = insertStmt.getGeneratedKeys();

		      if (generatedKeys.next()) {
		        long currentId = generatedKeys.getInt(1);
		        rechnung.setId(currentId);
		        rechnung.setLastModifiedTS(now);
		      }
		    }
		    catch (SQLException e) {
		      throw new DAOException("Rechnung could not be inserted!", e);
		    }
		    finally {
		      closeResultSet(generatedKeys);
		    }
		    return recordsAffected;
		  }
		  
	  private int update(Connection connection, Rechnung rechnung) {
		  validateConnection(connection);
		  validateRechnung(rechnung);

		  int recordsAffected = 0;
		  Date now = new Date();

		  try {
			  if (updateStmt == null) {
				  updateStmt = connection.prepareStatement(SQL_UPDATE);
			      }

			  updateStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			  updateStmt.setDate(2, JDBCHelper.toSqlDate(rechnung.getDate()));
			  updateStmt.setLong(3, rechnung.getId());
			  updateStmt.setTimestamp(4, JDBCHelper.toSqlTimestamp(rechnung.getLastModifiedTS()));

			  recordsAffected = updateStmt.executeUpdate();

			  rechnung.setLastModifiedTS(now);
		  }
		  catch (SQLException e) {
			  throw new DAOException("Rechnung could not be updated!", e);
		  }
		  return recordsAffected;
	  }
	  
	  public int delete(Connection connection, Rechnung rechnung) {
		  validateConnection(connection);
		  validateRechnung(rechnung);

		  int recordsAffected = 0;

		  try {
			  if (deleteStmt == null) {
				  deleteStmt = connection.prepareStatement(SQL_DELETE);
			  }

			  deleteStmt.setLong(1, rechnung.getId());
		      deleteStmt.setTimestamp(2, JDBCHelper.toSqlTimestamp(rechnung.getLastModifiedTS()));

		      recordsAffected = deleteStmt.executeUpdate();
		  }
		  catch (SQLException e) {
			  throw new DAOException("Rechnung could not be deleted!", e);
		  }
		  return recordsAffected;
	  }
	  
	  public Rechnung findById(Connection connection, Long id) {
		  validateConnection(connection);
		  if (id == null) throw new IllegalArgumentException("Id darf nicht null sein!");

		  Rechnung foundRechnung = null;
		  ResultSet rs = null;
		  try {
			  if (selectByIdStmt == null) {
				  selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
			  }

		      selectByIdStmt.setLong(1, id);
		      rs = selectByIdStmt.executeQuery();

		      while (rs.next()) {
		    	  foundRechnung = new Rechnung(rs. getLong("r_id"), rs.getLong("r_kundenid"), rs.getDate("r_datum").toString());
		    	  foundRechnung.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp("r_mod_ts")));
		      }
		  }
		  catch (SQLException e) {
			  throw new NotFoundException("Id could not be found!", e);
		  }
		  finally {
			  closeResultSet(rs);
		  }

		  return foundRechnung;
	  }	 
	  
	  public List<Rechnung> findAll(Connection connection) {
		  validateConnection(connection);

		  List<Rechnung> foundRechnungList = new ArrayList<Rechnung>();
		  Rechnung foundRechnung = null;
		  ResultSet rs = null;

		  try {
			  if (selectAllStmt == null) {
				  selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
		      }

			  rs = selectAllStmt.executeQuery();

		      while (rs.next()) {
		    	  foundRechnung = new Rechnung(rs.getLong("r_id"), rs.getLong("r_kundenid"), rs.getDate("r_datum").toString());
		    	  foundRechnung.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp("r_mod_ts")));
		    	  foundRechnungList.add(foundRechnung);
		      }
		  }
		  catch (SQLException e) {
			  throw new NotFoundException("RechnungList could not be read!", e);
		  }
		  finally {
			  closeResultSet(rs);
		  }

		  return foundRechnungList;
	  }	  
	  
	  public List<Rechnung> findByDate(Connection connection, String date) {
		  validateConnection(connection);

		  List<Rechnung> foundRechnungList = new ArrayList<Rechnung>();
		  Rechnung foundRechnung = null;
		  ResultSet rs = null;

		  try {
			  if (selectByDateStmt == null) {
				  selectByDateStmt = connection.prepareStatement(SQL_SELECT_BY_DATE);
		      }
			  
			  selectByDateStmt.setString(1, date);
			  rs = selectByDateStmt.executeQuery();

		      while (rs.next()) {
		    	  foundRechnung = new Rechnung(rs.getLong("r_id"), rs.getLong("r_kundenid"), rs.getDate("r_datum").toString());
		    	  foundRechnung.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp("r_mod_ts")));
		    	  foundRechnungList.add(foundRechnung);
		      }
		  }
		  catch (SQLException e) {
			  throw new NotFoundException("RechnungList could not be read!", e);
		  }
		  finally {
			  closeResultSet(rs);
		  }

		  return foundRechnungList;
	  }	
	  
	  public List<Rechnung> findByKundenID(Connection connection, Long kundenID) {
		  
		  if (kundenID == null) throw new IllegalArgumentException("KundenID darf nicht null sein!");
		  validateConnection(connection);

		  List<Rechnung> foundRechnungList = new ArrayList<Rechnung>();
		  Rechnung foundRechnung = null;
		  ResultSet rs = null;

		  try {
			  if (selectByKundenidStmt == null) {
				  selectByKundenidStmt = connection.prepareStatement(SQL_SELECT_BY_KUNDENID);
		      }
			  
			  selectByKundenidStmt.setLong(1, kundenID);
			  rs = selectByKundenidStmt.executeQuery();

			  while (rs.next()) {
		    	  foundRechnung = new Rechnung(rs.getLong("r_id"), rs.getLong("r_kundenid"), rs.getDate("r_datum").toString());
		    	  foundRechnung.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp("r_mod_ts")));
		    	  foundRechnungList.add(foundRechnung);
		      }
		  }
		  catch (SQLException e) {
			  throw new NotFoundException("RechnungList could not be read!", e);
		  }
		  finally {
			  closeResultSet(rs);
		  }

		  return foundRechnungList;
	  }	
		  
	  private void validateRechnung(Rechnung rechnung) {
		  if (rechnung == null) throw new IllegalArgumentException("Zu speichernde Rechnung darf nicht null sein!");
	  }
}